<!DOCTYPE html>

<?php 
	session_start(); 
	
	if(isset($_SESSION['id'], $_SESSION['vorname'], $_SESSION['name'])){
		$id = $_SESSION['id'];
		$vorname = $_SESSION['vorname'];
		$name = $_SESSION['name'];
	}
	
	function load_news(){
		include 'news.php';
	
		if(isset($_SESSION['workstation'])){
			$rank = $_SESSION['workstation'];
		}else{
			$rank = '0';
		}
		
		$news = get_news($rank,$link);
		
		while($row = mysqli_fetch_row($news)){
			echo "<tr><td>";
			echo "<table>";
			echo "<tr>";
			echo "<td width='150px'>"; echo "<h2>"; echo $row[1]; echo "</h2>"; echo "</td><td text-align:left>";
					 echo "-To: ";  if(isset($_SESSION['id'], $_SESSION['vorname'], $_SESSION['name']) && ($row[3]==$_SESSION['workstation'])) : RankName() ;
					  else : echo "Public"; endif;
			echo "</tr>";
			echo "<tr>"; echo "<td colspan='2'>"; echo $row[2]; echo "</td>"; echo "</tr>";
			echo "</table>";
			echo "</td></tr>";
		}
	}

	function RankName() {
		$sql = "USE web_db";
		$link = mysqli_connect('localhost', 'root', '') or die("Failed to connect to MySQL: " . 
				mysql_error());
		$result = mysqli_query($link,$sql);

		$sql = "SELECT workstation FROM rank WHERE rank_nr=".$_SESSION['workstation'];
		$result = mysqli_query($link,$sql);

		$row = mysqli_fetch_row($result);
		echo $row[0];
	}
?>

<html>
<head>
<link rel="stylesheet" type="text/css" href="./css/index.css">
<link href='./images/favicon.ico' rel='shortcut icon' type='image/png'>
<meta content='text/html; charset=UTF-8' http-equiv='Content-Type'/>
<title>Web Datenbanken</title>

</head>

<body id="fondo">

<div id="cuerpo">
<fieldset style="width: 1000px; color: #FFF;";>
	<?php
		include 'header.php';
	?>
    
    <div id="subcuerpo">
    	<table>
        <tr>
        	<td id="principal"><h2>Last News</h2></td>
			<?php if(isset($_SESSION['id'], $_SESSION['vorname'], $_SESSION['name'])) : ?>
			<td id="logged"><h2>Welcome back, <?php RankName(); echo " ".$name?></h2></n>
            				<table id="logout">
                				<tr><form method="POST" action="log_out.php">
								</tr>
                                <tr><td></td><td><input type="submit" name="submit" value="Logout"></td>
                				</form></tr>
                				<tr><td colspan='2'><?php PeopleSaved()?></td></tr>
                				</table></td>
			<?php else : ?>
            <td id="submenu"><h2>Log in</h2></n>
            				<table id="login">
                				<tr><td><form method="POST" action="login.php">ID: </td><td><input type="text" name="id"></td>
                				</tr>
                				<tr><td>Password: </td><td><input type="password" name="pwd"></td>
                                <tr><td></td><td><input type="submit" name="submit" value="Log in"></td>
                				</form></tr>
                				<tr>
                					<td colspan=2>If you are not a member yet, <a href="register.php">click here</a> to register now!
                					</td>
                				</tr>
                				</table></td>
			
			<?php endif; ?>
        </tr>
		<?php load_news(); ?>
        </table>
    </div>
    <?php
		include 'footer.php';
	?>


</fieldset>
</div>

</body>
</html>

<?php
function PeopleSaved() {
	$id=$_SESSION['id'];
	$sql = "USE web_db";
    $link = mysqli_connect('localhost', 'root', '');
    $result = mysqli_query($link,$sql);
    
	$sql = "SELECT m.mec_id, SUM(p.num_inhabit) 'Habitantes salvados'
			FROM mecha m, mech_mission mm, country_mission pm, country p
			WHERE m.mec_id=mm.mec_id AND mm.mission_nr=pm.mission_nr AND pm.code=p.code AND m.id='$id' GROUP BY m.mec_id";

    $result = mysqli_query($link,$sql);
    $aux = "";

    $row = mysqli_fetch_row($result);
    echo "<p></p>";
    echo "You saved ";
    if ($row[1]==NULL){
    	echo "0";
    } else {
    	echo "$row[1]";
    } 
    echo " inhabitants in your missions with ";
    if ($row[1]==NULL){
    	echo "any Mecha. Try it harder!";
    } else {
    	echo "$row[0]. Thanks!";
    }

}
?>